﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Wms.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Model.EnumUtil;

namespace Estsh.Core.Wms.Repositories
{
    public class OutsourceDeductRepository : BaseRepository<BaseEntity>, IOutsourceDeductRepository
    {
        public OutsourceDeductRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        /// <summary>
        /// 获取待扣减的数据
        /// </summary>
        /// <returns></returns>
        public List<WmsSubcontractData> GetOutsourceDeductInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                //获取关联库存中在线边库的库存信息
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select top 500 * from wms_subcontract_data (nolock) ");
                SqlStringBuilder.Append("where deal_status=10 ");
                SqlStringBuilder.Append("order by create_time ");

                return dbConn.Query<WmsSubcontractData>(SqlStringBuilder.ToString()).ToList();
            }
        }

        /// <summary>
        /// 处理待扣减的数据
        /// </summary>
        /// <param name="sapSu"></param>
        /// <returns></returns>
        public bool InsertOutsourceDeductInfo(List<WmsSubcontractData> wmsSubcontracts)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                WmsSubcontractData wmsSubcontract = new WmsSubcontractData();
                List<string> sqlList = new List<string>();
                List<DynamicParameters> parametersList = new List<DynamicParameters>();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                DynamicParameters parameters = new DynamicParameters();

                int firstCount = 0;//初始行
                int rowCount = 10000;//前10000行

                bool blLoop = true;
                while (blLoop)
                {
                    sqlList = new List<string>();
                    parametersList = new List<DynamicParameters>();

                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append(" select * from wms_subcontract_data (nolock) where deal_status=10 order by create_time,1 OFFSET " + firstCount + " ROWS FETCH NEXT " + rowCount + " ROWS ONLY  ");
                    List<WmsSubcontractData> wmsSubcontractDatas = dbConn.Query<WmsSubcontractData>(SqlStringBuilder.ToString()).ToList();

                    for (int i = 0; i < wmsSubcontractDatas.Count; i++)
                    {
                        WmsSubcontractData wmsSubcontractData = wmsSubcontractDatas[i];

                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("select * from sys_locate (nolock) where locate_type='90' enabled='Y' and order by create_time ");
                        List<SysLocate> sysLocates = dbConn.Query<SysLocate>(SqlStringBuilder.ToString()).ToList();
                        for (int j = 0; j < sysLocates.Count; j++)
                        {
                            sqlList = new List<string>();
                            parametersList = new List<DynamicParameters>();

                            SqlStringBuilder = new StringBuilder(1024);
                            SqlStringBuilder.Append("select * from sys_stock (nolock) where part_no='" + wmsSubcontract.PartNo + "' and locate_name='" + sysLocates[j].LocateName + "' and qty!=0 and enabled='Y' and order by create_time ");
                            SysStock stocks = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).FirstOrDefault();
                            if (stocks != null)
                            {
                                decimal remainQty = stocks.Qty - (wmsSubcontractData.Qty - wmsSubcontractData.DealQty == null ? 0 : wmsSubcontractData.DealQty);
                                decimal handledItemQty = 0;
                                if (remainQty >= 0)//库存数>=待扣减数
                                {
                                    SqlStringBuilder = new StringBuilder(1024);//扣减库存数
                                    SqlStringBuilder.Append(" update sys_stock set qty=@qty where ruid=@ruid");
                                    parameters = new DynamicParameters();
                                    parameters.Add("@qty", remainQty);
                                    parameters.Add("@ruid", stocks.Ruid);
                                    //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                    sqlList.Add(SqlStringBuilder.ToString());
                                    parametersList.Add(parameters);


                                    SqlStringBuilder = new StringBuilder(1024);//扣减库存数
                                    SqlStringBuilder.Append(" update wms_subcontract_data set deal_status=20 and deal_qty=" + wmsSubcontractData.Qty + " where ruid=ruid");
                                    parameters = new DynamicParameters();
                                    parameters.Add("@ruid", wmsSubcontractData.Ruid);
                                    //dbConn.Execute(SqlStringBuilder.ToString(),parameters);
                                    sqlList.Add(SqlStringBuilder.ToString());
                                    parametersList.Add(parameters);

                                    this.ExecuteTransaction(sqlList, parametersList);//执行
                                    break;
                                }
                                else//库存数<待扣减数
                                {
                                    SqlStringBuilder = new StringBuilder(1024);//扣减库存数
                                    SqlStringBuilder.Append(" update sys_stock set qty=0 , enabled='N' where ruid=" + stocks.Ruid);
                                    parameters = new DynamicParameters();
                                    parameters.Add("@ruid", wmsSubcontractData.Ruid);
                                    sqlList.Add(SqlStringBuilder.ToString());
                                    parametersList.Add(parameters);

                                    handledItemQty = Convert.ToDecimal(wmsSubcontractData.DealQty == null ? 0 : wmsSubcontractData.DealQty) + stocks.Qty;
                                    SqlStringBuilder = new StringBuilder(1024);//更新 已处理扣减数据
                                    SqlStringBuilder.Append(" update wms_subcontract_data set deal_qty=@dealQty where ruid=@ruid");
                                    parameters = new DynamicParameters();
                                    parameters.Add("@dealQty", handledItemQty);
                                    parameters.Add("@ruid", wmsSubcontractData.Ruid);
                                    sqlList.Add(SqlStringBuilder.ToString());
                                    parametersList.Add(parameters);

                                    this.ExecuteTransaction(sqlList, parametersList);//执行
                                    while (true)
                                    {
                                        sqlList = new List<string>();
                                        parametersList = new List<DynamicParameters>();

                                        SqlStringBuilder = new StringBuilder(1024);
                                        SqlStringBuilder.Append("select * from wms_subcontract_data (nolock) where ruid=" + wmsSubcontractData.Ruid);
                                        WmsSubcontractData wmsSubcontract1 = dbConn.Query<WmsSubcontractData>(SqlStringBuilder.ToString()).FirstOrDefault();

                                        if (wmsSubcontract1.Qty > wmsSubcontract1.DealQty)
                                        {
                                            SqlStringBuilder = new StringBuilder(1024);
                                            SqlStringBuilder.Append("select * from sys_stock (nolock) where part_no='" + wmsSubcontract.PartNo + "' and locate_name='" + sysLocates[j].LocateName + "' and qty!=0 and enabled='Y' and order by create_time ");
                                            stocks = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).FirstOrDefault();
                                            if (stocks != null)
                                            {
                                                remainQty = stocks.Qty - (wmsSubcontractData.Qty - wmsSubcontractData.DealQty == null ? 0 : wmsSubcontractData.DealQty);
                                                if (remainQty >= 0)
                                                {
                                                    SqlStringBuilder = new StringBuilder(1024);//扣减库存数
                                                    SqlStringBuilder.Append(" update sys_stock set qty=@qty where ruid=@ruid");
                                                    parameters = new DynamicParameters();
                                                    parameters.Add("@qty", remainQty);
                                                    parameters.Add("@ruid", stocks.Ruid);
                                                    sqlList.Add(SqlStringBuilder.ToString());
                                                    parametersList.Add(parameters);

                                                    SqlStringBuilder = new StringBuilder(1024);//扣减库存数
                                                    SqlStringBuilder.Append(" update wms_subcontract_data set deal_status=20 and deal_qty=@dealQty where ruid=@ruid");
                                                    parameters = new DynamicParameters();
                                                    parameters.Add("@ruid", wmsSubcontractData.Ruid);
                                                    sqlList.Add(SqlStringBuilder.ToString());
                                                    parametersList.Add(parameters);

                                                    this.ExecuteTransaction(sqlList, parametersList);//执行
                                                    break;
                                                }
                                                else
                                                {
                                                    SqlStringBuilder = new StringBuilder(1024);//扣减库存数
                                                    SqlStringBuilder.Append(" update sys_stock set qty=0 , enabled='N' where ruid=@ruid");
                                                    parameters = new DynamicParameters();
                                                    parameters.Add("@ruid", wmsSubcontractData.Ruid);
                                                    sqlList.Add(SqlStringBuilder.ToString());
                                                    parametersList.Add(parameters);

                                                    handledItemQty = Convert.ToDecimal(wmsSubcontractData.DealQty == null ? 0 : wmsSubcontractData.DealQty) + stocks.Qty;
                                                    SqlStringBuilder = new StringBuilder(1024);//更新 已处理扣减数据
                                                    SqlStringBuilder.Append(" update wms_subcontract_data set deal_qty=@dealQty where ruid=@ruid");
                                                    parameters = new DynamicParameters();
                                                    parameters.Add("@dealQty", handledItemQty);
                                                    parameters.Add("@ruid", wmsSubcontractData.Ruid);
                                                    sqlList.Add(SqlStringBuilder.ToString());
                                                    parametersList.Add(parameters);

                                                    this.ExecuteTransaction(sqlList, parametersList);//执行
                                                    continue;
                                                }
                                            }
                                            else
                                            {
                                                break;
                                            }
                                        }
                                        else
                                        {
                                            break;
                                        }
                                    }
                                    break;
                                }
                            }
                        }
                    }

       
                    SqlStringBuilder = new StringBuilder(1024);//获取待扣减行数
                    SqlStringBuilder.Append("  select count(1) as rowsCount from wms_subcontract_data (nolock) where deal_status=10 order by create_time ");
                    WmsSubcontractData wmsSubcontract2 = dbConn.Query<WmsSubcontractData>(SqlStringBuilder.ToString()).FirstOrDefault();

                    firstCount += 10000;
                    if (wmsSubcontract2.RowsCount <= firstCount)//当待扣减库存数据为0或循环行数>=待扣减行数，则跳出循环等待下一次作业
                    {
                        break;
                    }

                }

                return true;
            }
        }
    }
}
